#encoding: utf-8
load 'rds_api.rb' #get_xlsx_recs_from xlsx,fields
load 'tutem_utils.rb'

@rds = @rds_tt

# name:   rep_utils.rb
# usage:  客户代表相关操作库
# 1) import_repList: 将新的repList.xlsx导入系统；随xlsx文件格式应更改
# 2) initial_eb_rep：将现有的rep_code数据初始化到新建的eb_rep表中, 调用时带true参数将重置整个rep数据库


def repIdByCode
    return @repIdByCode if @repIdByCode
    @repIdByCode = {}
    sql = "select id, uid, code from tutem_mall.eb_rep"
    (queryRds(sql)).each do |q|
        @repIdByCode.store q['code'], q['id']
    end
    return @repIdByCode
end

def repNameByCode
    return @repNameByCode if @repNameByCode
    @repNameByCode = {}
    sql = "select id, uid, code, name from tutem_mall.eb_rep"
    (queryRds(sql)).each do |q|
        @repNameByCode.store q['code'], q['name']
    end
    return @repNameByCode
end

def repCodeByName
    return @repCodeByName if @repCodeByName
    @repCodeByName = {}
    sql = "select uid, name, code from tutem_mall.eb_rep"
    (queryRds(sql)).each do |q|
        @repCodeByName.store q['name'], q['code']
    end
    return @repCodeByName
end

def import_repQ3_from fn

    puts "import data from #{fn}..."

    repQ3_fields = [ 'uid', 'agency', 'real_name', 'phone', 'new_agency', 'new_phone', 'rep_name', 'final_rep_name' ]
    recs = get_xlsx_recs_from fn, repQ3_fields

    usqls = []
    isqls = []
    wmsgs = [] #准备发给企业微信的信息

    repList = {}
    recs.each do |r|

        uid = r['uid'].to_i
        rep_code = repCodeByName[r['final_rep_name']]
        agency = r['agency'].to_s
        phone = r['phone'].to_s
        agency = r['new_agency'].to_s if agency.empty?
        phone = r['new_phone'].to_s if phone.empty?

        usql = "update tutem_mall.eb_user set agency = '#{@rds.escape agency}', phone = '#{@rds.escape phone}', rep_code = '#{rep_code}' where uid = #{uid} "
        usqls += [usql]

    end

    commitTrans usqls
    puts "done"

end

def import_repList
    begin
        fn = "repQ3.xlsx"
        import_repQ3_from fn
    rescue => e
        puts "something wrong"
        puts e.message
    end
end

#用于构造最初的标准数据
def import_rep_db reset = false

    #默认不重置所有数据
    if reset
        sql = "delete from tutem_mall.eb_rep where 1=1"
        queryRds sql
        sql = "alter table tutem_mall.eb_rep auto_increment = 1"
        queryRds sql
    end

    #构造所需时间和备注
    now = Time.now
    add_time = now.to_i
    history = "[#{now.strftime('%y-%m-%d %H:%M:%S')}] oms created new record"

    #先创建系统数据
    isqls = []
    isqls += ["INSERT INTO `tutem_mall`.`eb_rep` ( `code`, `name`, `uid`, `aid`, `team`, `add_time`, `history`) VALUES ( 'SYS', '习同勇', 0, 0, 'SYSTEM', #{add_time}, '#{@rds.escape history}');"]
    commitTrans isqls

    #先选择所有员工的uid并导入eb_rep表中
    sql = "select uid, real_name, rep_code from tutem_mall.eb_user where is_promoter = 1 and promoter_type = 3;"
    usqls = []
    recorded_codes = []
    (queryRds(sql)).each do |q|
        uid = q['uid']
        code = q['rep_code']
        name = q['real_name']
        recorded_codes += [code]
        usql = "INSERT INTO `tutem_mall`.`eb_rep` (`code`, `name`, `uid`, `aid`, `team`, `add_time`, `history`) VALUES ('#{code}', '#{@rds.escape name}', #{uid}, 0, '', #{add_time}, '#{@rds.escape history}');"
        usqls += [usql]
    end
    commitTrans usqls

    #再检查剩余的rep_code
    missed_codes = []
    sql = "select rep_code from tutem_mall.eb_user where rep_code not in (select code from tutem_mall.eb_rep) group by rep_code;"
    (queryRds(sql)).each do |q| 
        missed_codes += [q['rep_code']]
    end
    ap missed_codes

    puts "记得根据实际部门设置及入职时间更新add_time以及team哦..." if reset

end

def init_rep_db

    statement = "delete from tutem_mall.eb_rep where 1=1;\n"
    statement += "alter table tutem_mall.eb_rep auto_increment = 1;\n"
    statement += "INSERT INTO `tutem_mall`.`eb_rep` (`id`, `code`, `name`, `uid`, `aid`, `team`, `add_time`, `history`) VALUES (1, 'SYS', '习同勇', 0, 0, 'SYSTEM', 1592070076, '[22-10-21 18:57:29] oms created new record');
INSERT INTO `tutem_mall`.`eb_rep` (`id`, `code`, `name`, `uid`, `aid`, `team`, `add_time`, `history`) VALUES (2, 'DXF', '董学锋', 1, 0, 'GMOFFC', 1606752000, '[22-10-21 18:57:29] oms created new record');
INSERT INTO `tutem_mall`.`eb_rep` (`id`, `code`, `name`, `uid`, `aid`, `team`, `add_time`, `history`) VALUES (3, 'LXX', '刘晓旭', 9, 0, 'SERVICE', 1592802264, '[22-10-21 18:57:29] oms created new record');
INSERT INTO `tutem_mall`.`eb_rep` (`id`, `code`, `name`, `uid`, `aid`, `team`, `add_time`, `history`) VALUES (4, 'WJ1', '吴婧', 31, 0, 'GMOFFC', 1592117874, '[22-10-21 18:57:29] oms created new record');
INSERT INTO `tutem_mall`.`eb_rep` (`id`, `code`, `name`, `uid`, `aid`, `team`, `add_time`, `history`) VALUES (5, 'WL1', '王蕾', 190, 0, 'GMOFFC', 1592120696, '[22-10-21 18:57:29] oms created new record');
INSERT INTO `tutem_mall`.`eb_rep` (`id`, `code`, `name`, `uid`, `aid`, `team`, `add_time`, `history`) VALUES (6, 'XTA', '小图啊', 764, 0, 'SERVICE', 1594916832, '[22-10-21 18:57:29] oms created new record');
INSERT INTO `tutem_mall`.`eb_rep` (`id`, `code`, `name`, `uid`, `aid`, `team`, `add_time`, `history`) VALUES (7, 'WYF', '王岩峰', 794, 0, 'GMOFFC', 1592117874, '[22-10-21 18:57:29] oms created new record');
INSERT INTO `tutem_mall`.`eb_rep` (`id`, `code`, `name`, `uid`, `aid`, `team`, `add_time`, `history`) VALUES (8, 'WJH', '吴建华', 2458, 0, 'SALES', 1650816000, '[22-10-21 18:57:29] oms created new record');
INSERT INTO `tutem_mall`.`eb_rep` (`id`, `code`, `name`, `uid`, `aid`, `team`, `add_time`, `history`) VALUES (9, 'CEC', '陈恩丞', 2887, 0, 'MKTING', 1638374400, '[22-10-21 18:57:29] oms created new record');
INSERT INTO `tutem_mall`.`eb_rep` (`id`, `code`, `name`, `uid`, `aid`, `team`, `add_time`, `history`) VALUES (10, 'PRB', '潘荣标', 3018, 0, 'SALES', 1634486400, '[22-10-21 18:57:29] oms created new record');
INSERT INTO `tutem_mall`.`eb_rep` (`id`, `code`, `name`, `uid`, `aid`, `team`, `add_time`, `history`) VALUES (11, 'WYH', '王艳华', 4061, 0, 'SERVICE', 1647273600, '[22-10-21 18:57:29] oms created new record');
INSERT INTO `tutem_mall`.`eb_rep` (`id`, `code`, `name`, `uid`, `aid`, `team`, `add_time`, `history`) VALUES (12, 'YH1', '杨海', 4498, 0, 'MKTING', 1649174400, '[22-10-21 18:57:29] oms created new record');
INSERT INTO `tutem_mall`.`eb_rep` (`id`, `code`, `name`, `uid`, `aid`, `team`, `add_time`, `history`) VALUES (13, 'YYC', '杨玉成', 5646, 0, 'DIMISSION', 1647792000, '[22-10-21 18:57:29] oms created new record');
INSERT INTO `tutem_mall`.`eb_rep` (`id`, `code`, `name`, `uid`, `aid`, `team`, `add_time`, `history`) VALUES (14, 'LY1', '林阳', 6355, 0, 'SALES', 1651680000, '[22-10-21 18:57:29] oms created new record');
INSERT INTO `tutem_mall`.`eb_rep` (`id`, `code`, `name`, `uid`, `aid`, `team`, `add_time`, `history`) VALUES (15, 'ZZB', '周资波', 7069, 0, 'SALES', 1654617600, '[22-10-21 18:57:29] oms created new record');
INSERT INTO `tutem_mall`.`eb_rep` (`id`, `code`, `name`, `uid`, `aid`, `team`, `add_time`, `history`) VALUES (16, 'FC1', '付成', 7180, 0, 'SALES', 1656604800, '[22-10-21 18:57:29] oms created new record');
INSERT INTO `tutem_mall`.`eb_rep` (`id`, `code`, `name`, `uid`, `aid`, `team`, `add_time`, `history`) VALUES (17, 'ZZZ', '朱泽章', 7527, 0, 'SALES', 1656604800, '[22-10-21 18:57:29] oms created new record');
INSERT INTO `tutem_mall`.`eb_rep` (`id`, `code`, `name`, `uid`, `aid`, `team`, `add_time`, `history`) VALUES (18, 'ZHZ', '郑慧真', 9429, 0, 'SALES', 1663689600, '[22-10-21 18:57:29] oms created new record');
"
    sqls = statement.split("\n")
    commitTrans sqls

end

#将当前数据库表中的rep指定关系配置到关系表中
def init_user_rep reset = false

    #默认不重置所有数据
    if reset
        sql = "delete from tutem_mall.eb_user_rep where 1=1"
        queryRds sql
        sql = "alter table tutem_mall.eb_user_rep auto_increment = 1"
        queryRds sql
    end

    #初始化备注
    add_time = Time.now.to_i
    history = "[#{Time.now.strftime('%y-%m-%d %H:%M:%S')}] OMS initiated relationship from current tutem_mall.eb_user"

    usqls = []
    #先读取目前所有有rep_code的promoter, age=0
    sql = "select u.uid as user_id, u.rep_code, r.id as rep_id, u.add_time as user_add_time, r.add_time as rep_add_time from tutem_mall.eb_user u left join tutem_mall.eb_rep r on u.rep_code = r.code where u.is_promoter = 1 and u.rep_code is not null"
    (queryRds(sql)).each do |q|
        since = q['user_add_time']
        since = q['rep_add_time'] if q['rep_add_time'] > q['user_add_time']
        usql = "insert into tutem_mall.eb_user_rep(user_id,rep_id,rep_code,since,add_time,history) values(#{q['user_id']},#{q['rep_id']},'#{q['rep_code']}',#{since},#{add_time},'#{@rds.escape history}');"
        usqls += [usql]
    end
    commitTrans usqls

end

def import_repQ3_from fn

    puts "import data from #{fn}..."

    repQ3_fields = [ 'uid', 'agency', 'real_name', 'phone', 'new_agency', 'new_phone', 'rep_name', 'final_rep_name' ]
    recs = get_xlsx_recs_from fn, repQ3_fields

    usqls = []
    isqls = []
    wmsgs = [] #准备发给企业微信的信息

    repList = {}
    recs.each do |r|

        uid = r['uid'].to_i
        rep_code = repCodeByName[r['final_rep_name']]
        agency = r['agency'].to_s
        phone = r['phone'].to_s
        agency = r['new_agency'].to_s if agency.empty?
        phone = r['new_phone'].to_s if phone.empty?

        usql = "update tutem_mall.eb_user set agency = '#{@rds.escape agency}', phone = '#{@rds.escape phone}', rep_code = '#{rep_code}' where uid = #{uid} "
        usqls += [usql]

    end

    commitTrans usqls
    puts "done"

end

def import_repQ4_excel

    fn = 'repQ4.xlsx'
    puts "import data from #{fn}..."

    repQ4_fields = [ 'uid', 'real_name', 'promoter_type', 'old_phone', 'old_agency_name', 'rep3Q', 'unionid', 'avatar', 'agency', 'department', 'title', 'phone', 'wxed', 'repQ4' ]
    recs = get_xlsx_recs_from fn, repQ4_fields

    withNewRepCode = 0
    invalidRepCode = 0
    keptRel = 0               #rep关系继续保持的专业用户数
    userUpdated = 0

    recs.each do |r|

        user_id = r['uid']
        rep_code = r['repQ4']
        rep_id = repIdByCode[rep_code]
        rep_name = repNameByCode[rep_code]
        since = Time.parse('2022-10-01 00:00:00').to_i #Q4开始时间

        recorded = promoterList[user_id]  #当前数据库中的记录
        usqls = [] #对eb_user进行更改的语句组

        #对于表中标注了有效repCode的记录进行增量处理
        if rep_id != nil

            rsqls = []
            withNewRepCode += 1

            #首先看该user_id与rep_id是否已经在eb_user_rep中存在且age=0，如果存在，则不更新user_rep关系 keptRel += 1
            #若不存在，则将age==0的记录的until更改为新关系的since；并将所有user_id和rep_id组合的age全部加一，再插入新记录
            sql = "select id from tutem_mall.eb_user_rep where user_id = #{user_id} and rep_id = #{rep_id} and age = 0"
            relation_actived = 0
            (queryRds(sql)).each { |q| relation_actived = q['id'] }

            if (relation_actived == 0)  #当前不存在此代表的服务关系
                log = "[#{Time.now.strftime('%y-%m-%d %H:%M:%S')}] OMS updated until_time for existed records using new relation via #{fn}"
                rsqls += ["update tutem_mall.eb_user_rep set until = #{since}, history = concat('#{@rds.escape log}',char(10),history) where user_id = #{user_id} and age = 0;"]
                log = "[#{Time.now.strftime('%y-%m-%d %H:%M:%S')}] OMS aged existed records using new relation via #{fn}"
                rsqls += ["update tutem_mall.eb_user_rep set age = age + 1, history =concat('#{@rds.escape log}',char(10),history) where user_id = #{user_id};"]
                history = "[#{Time.now.strftime('%y-%m-%d %H:%M:%S')}] OMS created new relation as actived via #{fn}"
                rsqls += ["insert into tutem_mall.eb_user_rep(user_id,rep_id,rep_code,rep_name,since,add_time,history) values(#{user_id},#{rep_id},'#{rep_code}','#{rep_name}', #{since},#{Time.now.to_i},'#{@rds.escape history}');"]
                #同时对eb_user表中的当前rep_code进行更新
                usqls += ["update tutem_mall.eb_user set rep_code = '#{rep_code}' where uid = #{user_id};"]
            else                        #当前已存在此代表的服务关系
                keptRel += 1
            end

            profile = r['wxed'] == 'Y' || r['wxed'] == 'y' ? 'WXED' : ''
            log = "[#{Time.now.strftime('%y-%m-%d %H:%M:%S')}] OMS updated profile for active relation via #{fn}"
            rsqls += ["update tutem_mall.eb_user_rep set profile = '#{@rds.escape profile}', history =concat('#{@rds.escape log}',char(10),history) where user_id = #{user_id} and rep_id = #{rep_id};"]

            commitTrans rsqls           #在这里执行吧

        else

            invalidRepCode += 1

        end

        #对于表中有增加机构、科室、职务的数据对用户表进行更新
        usqls += ["update tutem_mall.eb_user set agency = '#{r['agency']}' where uid = #{user_id};"] if recorded['agency'].to_s.empty? && r['agency'].to_s != ''
        usqls += ["update tutem_mall.eb_user set department = '#{r['department']}' where uid = #{user_id};"] if recorded['department'].to_s.empty? && r['department'].to_s != ''
        usqls += ["update tutem_mall.eb_user set phone = '#{r['phone']}' where uid = #{user_id};"] if recorded['phone'].to_s.empty? && r['phone'].to_s != ''
        usqls += ["update tutem_mall.eb_user set title = '#{r['title']}' where uid = #{user_id};"] if recorded['title'].to_s.empty? && r['title'].to_s != ''

        if usqls.size > 0
            commitTrans usqls
            userUpdated += 1
        end

    end

    puts "\ndone"
    puts "withNewRepCode: #{withNewRepCode}, keptRel: #{keptRel}, invalidRepCode: #{invalidRepCode} userUpdated: #{userUpdated}"

end

#【20221029-1】user_rep表中为了后台的显示方便，要增加一个当前rep_name的冗余数据
def update_rep_name
    sql = "UPDATE tutem_mall.eb_user_rep ur, tutem_mall.eb_rep r set ur.rep_name = r.name where ur.rep_id = r.id;"
    queryRds(sql)
    sql = "UPDATE tutem_mall.eb_user u, tutem_mall.eb_rep r set u.rep_id = r.id where u.rep_code = r.code and u.rep_code is not null;"
    queryRds(sql)
end

#将当前数据库表中的user_spreader关系配置到关系表中
def init_user_spreader reset = false

    #默认不重置所有数据
    if reset
        sql = "delete from tutem_mall.eb_user_spreader where 1=1"
        queryRds sql
        sql = "alter table tutem_mall.eb_user_spreader auto_increment = 1"
        queryRds sql
    end

    #初始化备注
    add_time = Time.now.to_i
    history = "[#{Time.now.strftime('%y-%m-%d %H:%M:%S')}] OMS initiated relationship from current tutem_mall.eb_user"

    usqls = []
    #先读取目前所有有spreader_code的promoter, age=0
    sql = "select u.uid as user_uid, u.spread_uid as spreader_uid, s.real_name as spreader_name, s.agency as spreader_agency, u.add_time as user_add_time, s.add_time as spreader_add_time from tutem_mall.eb_user u left join tutem_mall.eb_user s on u.spread_uid = s.uid"
    (queryRds(sql)).each do |q|
        since = q['user_add_time']
        spreader_uid = q['spreader_uid']
        spreader_name = q['spreader_name'].to_s
        spreader_agency = q['spreader_agency'].to_s
        spreader_name = 'SYS' if spreader_uid == 0
        spreader_agency = 'SYS' if spreader_uid == 0
        usql = "insert into tutem_mall.eb_user_spreader(user_uid,spreader_uid,spreader_name, spreader_agency, since,add_time,history) values(#{q['user_uid']},#{spreader_uid},'#{@rds.escape spreader_name}','#{@rds.escape spreader_agency}',#{since},#{add_time},'#{@rds.escape history}');"
        usqls += [usql]
    end

    #commitTrans usqls

    idx = 0
    txs = []
    usqls.each do |usql|
        txs += [usql]
        if idx == 999
            commitTrans txs
            txs = []
            idx = 0
        else
          idx += 1
        end
    end
    commitTrans txs

end
#init_user_spreader true

def change_user_spreader user_uid, new_spreader_uid, since = Time.now.to_i, skip_np = true

    usqls = []

    #先获取当前spreader_uid
    current_spreader_uid = 0
    current_spreader_name = 'SYS'
    current_spreader_agency = 'SYS'
    current_user_add_time = Time.now.to_i
    sql = "select u.uid, u.spread_uid, u.add_time, s.real_name as spreader_name, s.agency as spreader_agency from tutem_mall.eb_user u left join tutem_mall.eb_user s on (u.spread_uid = s.uid) where u.uid = #{user_uid};"
    #puts "getting current spreader uid..."; puts sql
    (queryRds(sql)).each do |q|
        spreader_uid = q['spread_uid']
        current_spreader_uid = q['spread_uid']
        current_spreader_name = q['spreader_name'].to_s
        current_spreader_agency = q['spreader_agency'].to_s
        current_spreader_name = 'SYS' if spreader_uid == 0
        current_spreader_agency = 'SYS' if spreader_uid == 0
        current_user_add_time = q['add_time']
    end

    #当前推广关系如果没有纪录为活跃关系，需要先插入纪录
    current_relation_actived = false
    sql = "select id from tutem_mall.eb_user_spreader where user_uid = #{user_uid} and spreader_uid = #{current_spreader_uid} and age = 0"
    #puts "checking if current spreader relation actived..."; puts sql
    (queryRds(sql)).each { |q| current_relation_actived = true }

    if current_relation_actived == false

        #如果存在其他纪录关系，统统做一次老化，以便增加纪录当前推广关系
        log = "[#{Time.now.strftime('%y-%m-%d %H:%M:%S')}] OMS aged existed records to record current relation(#{current_spreader_uid}->#{user_uid})"
        sql = "update tutem_mall.eb_user_spreader set age = age + 1, history =concat('#{@rds.escape log}',char(10),history) where user_uid = #{user_uid};"
        #puts 'aging existed spreader relationship'; puts sql
        queryRds(sql)

        #增写当前激活关系
        history = "[#{Time.now.strftime('%y-%m-%d %H:%M:%S')}] OMS added current relation(#{current_spreader_uid}->#{user_uid}) from eb_user to eb_user_spreader"
        sql = "insert into tutem_mall.eb_user_spreader(user_uid,spreader_uid,spreader_name, spreader_agency, since,add_time,history) values(#{user_uid},#{current_spreader_uid},'#{@rds.escape current_spreader_name}','#{@rds.escape current_spreader_agency}',#{current_user_add_time},#{Time.now.to_i},'#{@rds.escape history}');"
        #puts 'adding current relationship as actived before insert new relation'; puts sql
        queryRds(sql)

    end

    #获取新推广人的冗余信息方便增加纪录
    new_spreader_name = 'SYS'
    new_spreader_agency = 'SYS'
    sql = "select uid, real_name, agency from tutem_mall.eb_user where uid = #{new_spreader_uid};"
    #puts 'checking new spreader...'; puts sql
    (queryRds(sql)).each { |q| new_spreader_name = q['real_name'].to_s; new_spreader_agency = q['agency'].to_s }

    #首先看该user_uid与spreader_uid是否已经在eb_user_spreader中存在且age=0，如果存在，则不更新user_spreader关系
    #若不存在，则将age==0的记录的until更改为新关系的since；并将所有user_uid和spreader_uid组合的age全部加一，再插入新记录
    sql = "select id from tutem_mall.eb_user_spreader where user_uid = #{user_uid} and spreader_uid = #{new_spreader_uid} and age = 0"
    relation_actived = 0
    (queryRds(sql)).each { |q| relation_actived = q['id'] }
    #puts "checking if current relationship actived: #{relation_actived}";

    if (relation_actived == 0)  #当前不存在此推广的锁定关系
        log = "[#{Time.now.strftime('%y-%m-%d %H:%M:%S')}] OMS updated until_time for realtion(#{current_spreader_uid}->#{user_uid}) via relation(#{new_spreader_uid}->#{user_uid})"
        usqls += ["update tutem_mall.eb_user_spreader set until = #{since}, history = concat('#{@rds.escape log}',char(10),history) where user_uid = #{user_uid} and age = 0;"]
        log = "[#{Time.now.strftime('%y-%m-%d %H:%M:%S')}] OMS aged existed records to active new relation(#{new_spreader_uid}->#{user_uid})"
        usqls += ["update tutem_mall.eb_user_spreader set age = age + 1, history =concat('#{@rds.escape log}',char(10),history) where user_uid = #{user_uid};"]
        history = "[#{Time.now.strftime('%y-%m-%d %H:%M:%S')}] OMS created new relation(#{new_spreader_uid}->#{user_uid})"
        usqls += ["insert into tutem_mall.eb_user_spreader(user_uid,spreader_uid,spreader_name,spreader_agency,since,add_time,history) values(#{user_uid},#{new_spreader_uid},'#{@rds.escape new_spreader_name}','#{@rds.escape new_spreader_agency}', #{since},#{Time.now.to_i},'#{@rds.escape history}');"]
        #同时对eb_user表中的当前spreader_code进行更新
        if skip_np #属于同一用户的情况，需要设置用户为不做单独的首单统计
            snv = 9999999 #skip_np_value
            snv = current_spreader_uid if current_spreader_uid > 0 #对于上级为零的用户需要设置一个默认值
            usqls += ["update tutem_mall.eb_user set spread_uid = #{new_spreader_uid}, skip_np = #{snv}, is_qp = -1 where uid = #{user_uid};"] if new_spreader_uid != current_spreader_uid
        else
            usqls += ["update tutem_mall.eb_user set spread_uid = #{new_spreader_uid}, skip_np = 0, is_qp = 0 where uid = #{user_uid};"] if new_spreader_uid != current_spreader_uid
        end
    else                        #当前已存在此推广的锁定关系
        puts "skipped change"
    end

    commitTrans usqls

    syncUserData user_uid

end

#订单是否已经发放过图贝,通过检查link_id的 order_id#nnn 的形式，所有的附加奖励都在link_id中用政策号来走
def order_rebated? order_id
    rebated = true
    link_info = "order_id##{order_id}"
    sqls = "select count(*) as recs from tutem_mall.eb_user_bill where link_id like '#{link_info}' and category = 'now_money' and pm = 1"
    res = queryRds sqls
    res.each do |r|
        rebated = false if r['recs'] > 0
    end
    return rebated
end

def rebate_order order_id

    if order_rebated? order_id
        puts "[rebate_order #{order_id}] order has been rebated !!"
        return false
    end

    spreader_uid = 0
    to_rebate = 0.00
    actual_paid = 0.00
    sql = "select o.uid, u.spread_uid as spreader_uid, o.pay_price, o.refund_price from tutem_mall.eb_store_order o left join tutem_mall.eb_user u on (o.uid = u.uid) where o.id = #{order_id} and o.paid = 1 and o.pay_type = 'weixin'"
    (queryRds(sql)).each do |q|
        spreader_uid = q['spreader_uid']
        actual_paid = (q['pay_price'] - q['refund_price']).to_f.round(2)
        to_rebate = (actual_paid * 0.16).to_f.round(2)
    end

    if to_rebate <= 0
        puts "[rebate_order #{order_id}] to_rebate == 0 !!" if rebated
        return false
    end

    if spreader_uid == 0
        puts "[rebate_order #{order_id}] spreader_uid == 0 !!" if rebated
        return false
    end

    current_balance =  0.00
    sql = "select now_money from tutem_mall.eb_user where uid = #{spreader_uid}"
    (queryRds(sql)).each { |q| current_balance = q['now_money'] }

    puts "[rebate_order #{order_id}] spreader #{spreader_uid} current balance is #{current_balance.to_f.round(2)}"

    new_balance = (current_balance + to_rebate).to_f.round(2)

        sqls = []
        sqls += ["update tutem_mall.eb_user set now_money = #{new_balance} where uid = #{spreader_uid}"]

        msg = "OMS结算图贝：订单#{order_id}现金实收额#{actual_paid}元，赠送图贝#{to_rebate}"
        link_info = "order_id##{order_id}"

        add_time = Time.now.to_i

        sqls += ["INSERT INTO tutem_mall.eb_user_bill ( uid, link_id, pm, title, category, type, number, balance, mark, add_time, status, take) VALUES ( #{spreader_uid}, '#{link_info}', 1, '附加奖励政策20210711L', 'now_money', 'brokerage', #{to_rebate}, #{new_balance}, '#{msg}', #{add_time}, 1, 0);"]

        #commitTrans sqls
        ap sqls

    return true
end

#检查变更了推广上级的用户，对其产生过的有实际支付的订单，如果当前上级是T1，则给予16%的图贝分润
def verify_qualified_t1_tupal

    #查找有过age纪录的user_uid，并找出对应的age=0的新上级为P1T1会员的部分
    recs = {}
    sql = "SELECT id, user_uid, spreader_uid, age, since, from_unixtime(since) as since_at, until, from_unixtime(until) as until_at, from_unixtime(add_time) as add_at, order_updated from tutem_mall.eb_user_spreader where user_uid in (select user_uid from tutem_mall.eb_user_spreader where age > 0) and age = 0 and spreader_uid in (select uid from tutem_mall.eb_user where is_promoter = 1 and promoter_type = 1) "
    (queryRds(sql)).each do |q|
        uid = q['user_uid']
        spreader_uid = q['spreader_uid']
        rec = {'uid'=>uid, 'spreader_uid'=>spreader_uid, 'order_ids'=>[]}
        recs.store uid, rec
    end

    sqla = "SELECT user_uid from tutem_mall.eb_user_spreader where user_uid in (select user_uid from tutem_mall.eb_user_spreader where age > 0) and age = 0 and spreader_uid in (select uid from tutem_mall.eb_user where is_promoter = 1 and promoter_type = 1) "
    sqlb = "select * from tutem_mall.eb_store_order where paid = 1 and pay_type = 'weixin' and uid in (#{sqla})"
    order_ids = []
    (queryRds(sqlb)).each do |q|
        uid = q['uid']
        rec = recs[uid]
        order_ids = rec['order_ids']
        order_ids += [q['id']]
        rec.store 'order_ids', order_ids
        recs.store uid, rec
    end

    qualified_amount = 0

    recs.each do |uid, rec|
        spreader_uid = rec['spreader_uid']
        order_ids = rec['order_ids']
        msg = "为归转P1T1会员C##{spreader_uid}的用户U##{uid}所支付的订单补发图贝至当前关联专业会员\n"
        puts msg
        order_ids.each do |order_id|
            next if order_bonus_settled? order_id
            sql = "select * from tutem_mall.eb_store_order where id = #{order_id}"
            to_incentive = 0
            (queryRds(sql)).each do |q|
                to_incentive = ( ( q['pay_price'] - q['refund_price'] ) * 0.16).to_f.round(2)
            end
            qualified_amount += to_incentive
            puts "order ##{order_id}: #{to_incentive}"
        end
    end

    return recs

end

#verify_qualified_t1_tupal
#rebate_order 10271
#rebate_order 6670
